OpenEdge Data Management:<br />DataServer for ORACLE Run Stored-Proc statement with send-sql-statement option

The Progress 4GL also allows you to use stored-procedure syntax to send SQL statements and their native language extensions directly to a data source. The DataServer uses the RUN STORED–PROCEDURE statement with the send–sql–statement option to pass SQL statements to the data source. However, the ORACLE DataServer only supports one SQL statement or PL/SQL block with the send-sql-statement option.

Like the option to define a RUN STORED-PROC statement either with or without the LOAD-RESULT-INTO options, you can define a send-sql-statement option with or without the LOAD-RESULT-INTO option. The following section presents and briefly describes an example use of each technique.

Without the Load-Result-Into option

You use the RUN STORED–PROC statement with the send–sql–statement option and pass the PL/SQL statements as a parameter. The syntax of the statement must be valid PL/SQL syntax. Example 3–1 shows how the code presented passes a SELECT statement as a parameter.

DEFINE VAR h1 AS INTEGER. DEFINE VAR j AS INTEGER. RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR ("select cust_num,name like ‘A_’”). FOR EACH proc-text-buffer WHERE PROC-HANDLE = h1: DISPLAY proc-text-buffer. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.

DEFINE VAR h1 AS INTEGER. 
DEFINE VAR j AS INTEGER.
RUN STORED-PROC send-sql-statement h1 = PROC-HANDLE NO-ERROR
 ("select cust_num,name like ‘A_’”).
FOR EACH proc-text-buffer WHERE PROC-HANDLE = h1:
	 DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = h1.

This example returns the name of all customers whose name begins with A. You must read the results into a buffer as you would with a stored procedure called by a Progress procedure. You can read the results into the proc–text–buffer defined by Progress as shown in the example above. Alternatively, you can define your own buffer from within your data source that can accept other data type as well as the CHARACTER data type.

With the Load-Result-Into option

Example 3–2 shows how to use the send-sql-statement option with the LOAD-RESULT-INTO option. It also shows that the PROC-STATUS phrase must be defined as part of the RUN STORED-PROC statement because of the implicit CLOSE STORED-PROC that is associated with the LOAD-RESULT-INTO phrase.

DEFINE VAR ttHndl AS HANDLE. DEFINE TEMP-TABLE tt1 FIELD f1 AS INTEGER FIELD f2 AS CHAR. ttHndl=TEMP-TABBLE tt1:HANDLE. RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tthndl (“select cust_num, name like ‘A_’”).

DEFINE VAR ttHndl AS HANDLE. 
DEFINE TEMP-TABLE tt1 
FIELD f1 AS INTEGER 
FIELD f2 AS CHAR. 
ttHndl=TEMP-TABBLE tt1:HANDLE. 
RUN STORED-PROC send-sql-statement LOAD-RESULT-INTO tthndl  
     (“select cust_num, name like ‘A_’”).

Example 3–2: Using the send-sql-statement option with LOAD-RESULT-INTO option

In the previous example, also note that the PROC-STATUS phrase does not need a PROC-HANDLE phrase because it is retrieved using the RUN STORED-PROC statement; although the PROC-HANDLE is typically used after the execution of the RUN STORED-PROC statement, it is not needed in this context.